package com.assetmanage.dao.impl;

import com.assetmanage.dao.BorrowDao;
import com.assetmanage.model.Borrow;
import com.assetmanage.utils.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Date;

public class BorrowDaoImpl implements BorrowDao {
    @Override
    public List<Borrow> getAllBorrows() {
        String sql = "SELECT b.*, u.u_name, a.asset_name, d.dep_name " +
                    "FROM borrow b " +
                    "JOIN users u ON b.u_id = u.u_id " +
                    "JOIN asset a ON b.asset_id = a.asset_id " +
                    "JOIN departments d ON u.dep_id = d.dep_id " +
                    "ORDER BY b.borrow_id DESC";
        
        List<Borrow> borrows = new ArrayList<>();
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        
        try {
            conn = DBUtil.getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            
            while (rs.next()) {
                borrows.add(extractBorrowFromResultSet(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, stmt, rs);
        }
        
        return borrows;
    }

    @Override
    public List<Borrow> getBorrowsByUser(int uid) {
        String sql = "SELECT b.*, u.u_name, a.asset_name, d.dep_name " +
                    "FROM borrow b " +
                    "JOIN users u ON b.u_id = u.u_id " +
                    "JOIN asset a ON b.asset_id = a.asset_id " +
                    "JOIN departments d ON u.dep_id = d.dep_id " +
                    "WHERE b.u_id = ? " +
                    "ORDER BY b.borrow_id DESC";
        
        List<Borrow> borrows = new ArrayList<>();
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        
        try {
            conn = DBUtil.getConnection();
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, uid);
            rs = stmt.executeQuery();
            
            while (rs.next()) {
                borrows.add(extractBorrowFromResultSet(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, stmt, rs);
        }
        
        return borrows;
    }

    @Override
    public List<Borrow> getBorrowsByAsset(String assetId) {
        String sql = "SELECT b.*, u.u_name, a.asset_name, d.dep_name " +
                    "FROM borrow b " +
                    "JOIN users u ON b.u_id = u.u_id " +
                    "JOIN asset a ON b.asset_id = a.asset_id " +
                    "JOIN departments d ON u.dep_id = d.dep_id " +
                    "WHERE b.asset_id = ? " +
                    "ORDER BY b.borrow_id DESC";
        
        List<Borrow> borrows = new ArrayList<>();
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        
        try {
            conn = DBUtil.getConnection();
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, assetId);
            rs = stmt.executeQuery();
            
            while (rs.next()) {
                borrows.add(extractBorrowFromResultSet(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, stmt, rs);
        }
        
        return borrows;
    }

    @Override
    public boolean addBorrow(Borrow borrow) {
        String sql = "INSERT INTO borrow (u_id, asset_id, borrow_status, borrow_num, " +
                    "borrow_time, expected_return_time, borrow_reason) " +
                    "VALUES (?, ?, ?, ?, ?, ?, ?)";
        
        Connection conn = null;
        PreparedStatement stmt = null;
        
        try {
            conn = DBUtil.getConnection();
            stmt = conn.prepareStatement(sql);
            
            stmt.setInt(1, borrow.getUId());
            stmt.setString(2, borrow.getAssetId());
            stmt.setString(3, borrow.getBorrowStatus());
            stmt.setInt(4, borrow.getBorrowNum());
            stmt.setDate(5, new java.sql.Date(borrow.getBorrowTime().getTime()));
            stmt.setDate(6, new java.sql.Date(borrow.getExpectedReturnTime().getTime()));
            stmt.setString(7, borrow.getBorrowReason());
            
            return stmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            DBUtil.close(conn, stmt, null);
        }
    }

    @Override
    public boolean updateBorrow(Borrow borrow) {
        String sql = "UPDATE borrow SET u_id = ?, asset_id = ?, borrow_status = ?, " +
                    "borrow_num = ?, borrow_time = ?, expected_return_time = ?, " +
                    "borrow_reason = ? WHERE borrow_id = ?";
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = DBUtil.getConnection();
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, borrow.getUId());
            stmt.setString(2, borrow.getAssetId());
            stmt.setString(3, borrow.getBorrowStatus());
            stmt.setInt(4, borrow.getBorrowNum());
            stmt.setDate(5, new java.sql.Date(borrow.getBorrowTime().getTime()));
            stmt.setDate(6, new java.sql.Date(borrow.getExpectedReturnTime().getTime()));
            stmt.setString(7, borrow.getBorrowReason());
            stmt.setInt(8, borrow.getBorrowId());
            return stmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            DBUtil.close(conn, stmt, null);
        }
    }

    @Override
    public boolean deleteBorrow(int borrowId) {
        String sql = "DELETE FROM borrow WHERE borrow_id = ?";
        Connection conn = null;
        PreparedStatement stmt = null;
        
        try {
            conn = DBUtil.getConnection();
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, borrowId);
            System.out.println("执行删除SQL: " + sql + ", borrowId = " + borrowId);
            int result = stmt.executeUpdate();
            System.out.println("删除操作影响的行数: " + result);
            return result > 0;
        } catch (SQLException e) {
            System.err.println("删除借用记录时发生SQL异常: " + e.getMessage());
            e.printStackTrace();
            return false;
        } finally {
            DBUtil.close(conn, stmt, null);
        }
    }

    @Override
    public Borrow getBorrowById(int borrowId) {
        String sql = "SELECT b.*, u.u_name, a.asset_name, d.dep_name " +
                    "FROM borrow b " +
                    "JOIN users u ON b.u_id = u.u_id " +
                    "JOIN asset a ON b.asset_id = a.asset_id " +
                    "JOIN departments d ON u.dep_id = d.dep_id " +
                    "WHERE b.borrow_id = ?";
        
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        
        try {
            conn = DBUtil.getConnection();
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, borrowId);
            System.out.println("执行查询SQL: " + sql + ", borrowId = " + borrowId);
            rs = stmt.executeQuery();
            
            if (rs.next()) {
                return extractBorrowFromResultSet(rs);
            }
        } catch (SQLException e) {
            System.err.println("查询借用记录时发生SQL异常: " + e.getMessage());
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, stmt, rs);
        }
        
        return null;
    }

    @Override
    public boolean updateBorrowStatus(int borrowId, String status) {
        String sql = "UPDATE borrow SET borrow_status = ?, actual_return_time = ? WHERE borrow_id = ?";
        Connection conn = null;
        PreparedStatement stmt = null;
        
        try {
            conn = DBUtil.getConnection();
            stmt = conn.prepareStatement(sql);
            
            stmt.setString(1, status);
            if ("已归还".equals(status)) {
                stmt.setDate(2, new java.sql.Date(System.currentTimeMillis()));
            } else {
                stmt.setNull(2, Types.DATE);
            }
            stmt.setInt(3, borrowId);
            
            return stmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            DBUtil.close(conn, stmt, null);
        }
    }

    @Override
    public List<Borrow> getUsageStats(Date startDate, Date endDate, String depId) {
        String sql = "SELECT b.*, a.asset_name, d.dep_name " +
                    "FROM borrow b " +
                    "JOIN asset a ON b.asset_id = a.asset_id " +
                    "JOIN users u ON b.u_id = u.u_id " +
                    "JOIN departments d ON u.dep_id = d.dep_id " +
                    "WHERE b.borrow_time BETWEEN ? AND ? ";
        
        if (!"all".equals(depId)) {
            sql += "AND u.dep_id = ? ";
        }
        
        sql += "ORDER BY b.borrow_time DESC";
        
        List<Borrow> borrows = new ArrayList<>();
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        
        try {
            conn = DBUtil.getConnection();
            
            // 打印SQL语句和参数用于调试
            System.out.println("执行SQL: " + sql);
            System.out.println("参数1 (startDate): " + startDate);
            System.out.println("参数2 (endDate): " + endDate);
            if (!"all".equals(depId)) {
                System.out.println("参数3 (depId): " + depId);
            }
            
            stmt = conn.prepareStatement(sql);
            stmt.setDate(1, new java.sql.Date(startDate.getTime()));
            stmt.setDate(2, new java.sql.Date(endDate.getTime()));
            
            if (!"all".equals(depId)) {
                stmt.setString(3, depId);
            }
            
            rs = stmt.executeQuery();
            while (rs.next()) {
                Borrow borrow = extractBorrowFromResultSet(rs);
                // 打印每条记录的关键信息
                System.out.println("读取到记录: " +
                    "ID=" + borrow.getBorrowId() + 
                    ", 时间=" + borrow.getBorrowTime() + 
                    ", 状态=" + borrow.getBorrowStatus() + 
                    ", 资产=" + borrow.getAssetName() + 
                    ", 部门=" + borrow.getDepName());
                borrows.add(borrow);
            }
            
            // 打印查询结果数量
            System.out.println("查询到 " + borrows.size() + " 条记录");
            
        } catch (SQLException e) {
            System.err.println("SQL执行出错: " + e.getMessage());
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, stmt, rs);
        }
        
        return borrows;
    }

    // 从ResultSet中提取Borrow对象的辅助方法
    private Borrow extractBorrowFromResultSet(ResultSet rs) throws SQLException {
        Borrow borrow = new Borrow();
        borrow.setBorrowId(rs.getInt("borrow_id"));
        borrow.setUId(rs.getInt("u_id"));
        borrow.setAssetId(rs.getString("asset_id"));
        borrow.setBorrowStatus(rs.getString("borrow_status"));
        borrow.setBorrowNum(rs.getInt("borrow_num"));
        
        // 使用ISO格式的日期
        java.sql.Date borrowTime = rs.getDate("borrow_time");
        if (borrowTime != null) {
            borrow.setBorrowTime(new java.util.Date(borrowTime.getTime()));
        }
        
        java.sql.Date expectedReturnTime = rs.getDate("expected_return_time");
        if (expectedReturnTime != null) {
            borrow.setExpectedReturnTime(new java.util.Date(expectedReturnTime.getTime()));
        }
        
        java.sql.Date actualReturnTime = rs.getDate("actual_return_time");
        if (actualReturnTime != null) {
            borrow.setActualReturnTime(new java.util.Date(actualReturnTime.getTime()));
        }
        
        borrow.setBorrowReason(rs.getString("borrow_reason"));
        
        // 设置关联查询的字段并打印调试信息
        try {
            String uName = rs.getString("u_name");
            System.out.println("从数据库读取到的用户名: " + uName);
            borrow.setUName(uName);
        } catch (SQLException e) {
            System.err.println("获取用户名失败: " + e.getMessage());
        }
        
        try {
            String assetName = rs.getString("asset_name");
            System.out.println("从数据库读取到的资产名: " + assetName);
            borrow.setAssetName(assetName);
        } catch (SQLException e) {
            System.err.println("获取资产名失败: " + e.getMessage());
        }
        
        try {
            String depName = rs.getString("dep_name");
            System.out.println("从数据库读取到的部门名: " + depName);
            borrow.setDepName(depName);
        } catch (SQLException e) {
            System.err.println("获取部门名失败: " + e.getMessage());
        }
        
        return borrow;
    }
} 